/**
 * 
 */
package metier.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import metier.*;

import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author Quipu-Team
 *
 */
public class EnseignantJDBC extends Enseignant {

    private final static String NOM = "Nom_Enseignant";
    private final static String PRENOM = "Prenom_Enseignant";
    private final static String STATUT = "StatutEnseignant";
    private final static String ID = "ID_Enseignant";
    private final static String NOMTABLE = "Enseignant";
    
    private final static String idQuery = "select seq_enseignant.nextval from dual";
    private final static String insertQuery = "insert into " + NOMTABLE + " values(?, ?, ?, ?)";
    private final static String updateQuery = "update "
            + NOMTABLE + " set "
            + STATUT + " = ?, "
            + NOM + " = ?, "
            + PRENOM + " = ? where "
            + ID + " = ?";
    private final static String deleteQuery = "delete from " + NOMTABLE + " where " + ID + " = ?";
    private final static String loadQuery = "select * from " + NOMTABLE + " where " + ID + " = ?";
    private final static String selectQuery = "select * from " + NOMTABLE;

    /**
     * @param nom
     * @param prenom
     * @param statut
     */
    public EnseignantJDBC(String nom, String prenom, StatutEnseignant statut) {
        super(nom, prenom, statut);
    }

    public EnseignantJDBC(){
        super();
    }

    /**
     * 
     * @return Id
     * @throws ExceptionMetier
     */
    protected int createId() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            Statement sta = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ResultSet rset = sta.executeQuery(idQuery);
            if (!rset.next()) {
                throw new ExceptionMetier("creating new id failed due to non generated id");
            }
            int newId = rset.getInt(1);
            rset.close();
            sta.close();
            return newId;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("creating new id failed", ex);
        }
    }

    /**
     * @see metier.Enseignant#insert()
     */
    @Override
    public void insert() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement insertEnseignant = con.prepareStatement(insertQuery);
            if (getId() == 0) {
                setId(createId());
            }
            insertEnseignant.setInt(1, this.getId());
            insertEnseignant.setInt(2, this.getStatut().getId());
            insertEnseignant.setString(3, this.getNom());
            insertEnseignant.setString(4, this.getPrenom());
            insertEnseignant.executeQuery();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Insert failed, ClassNotFoundException", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Insert failed, SQLException", ex);
        }
    }

    /**
     * @see metier.Enseignant#update()
     */
    @Override
    public void update() throws ExceptionMetier {
        try {
            Connection con = ConnectionJDBC.getConnection();

            PreparedStatement updateE = con.prepareStatement(updateQuery);
            updateE.setInt(1, getStatut().getId());
            updateE.setString(2, getNom());
            updateE.setString(3, getPrenom());
            updateE.setInt(4, getId());
            updateE.executeUpdate();

        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Update failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Update failed", ex);
        }
    }

    /**
     * @throws ExceptionMetier 
     * @see metier.Enseignant#delete()
     */
    @Override
    public void delete() throws ExceptionMetier{
        try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement deleteEnseignant = con.prepareStatement(deleteQuery);
            deleteEnseignant.setInt(1, this.getId());
            deleteEnseignant.executeQuery();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Delete failed", ex);
        }
    }

    public void load(int id) throws ExceptionMetier {
        try {
            setId(id);
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement loadEnseignant = con.prepareStatement(loadQuery);
            loadEnseignant.setInt(1, getId());
            ResultSet rset = loadEnseignant.executeQuery();
            if (!rset.next()) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            if (rset.getInt(ID) != getId()) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            StatutEnseignant st = new StatutEnseignantJDBC();
            st.load(rset.getInt(STATUT));
            this.setStatut(st);
            this.setNom(rset.getString(NOM));
            this.setPrenom(rset.getString(PRENOM));
            rset.close();
            loadEnseignant.close();
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Load failed, ClassNotFoundException", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Load failed, SQLexception", ex);
        }
    }

    /**
     * Methode qui retourne la liste des enseignants dans le syst�me de stockage
     * @return ResultSet liste des enseignants sous forme d'un resultSet
     * @throws ExceptionMetier
     */
    public static ArrayList<ElementListe> select() throws ExceptionMetier {
        try {
            ArrayList<ElementListe> listeEnseignant = new ArrayList<ElementListe>();
            ResultSet rset = null;
            Connection con = ConnectionJDBC.getConnection();
            Statement sta = con.createStatement();
            rset = sta.executeQuery(selectQuery);
            while (rset.next()) {
                listeEnseignant.add(new ElementListe(rset.getInt(ID), rset.getString(NOM)));
            }
            rset.close();
            sta.close();
            return listeEnseignant;
        } catch (ClassNotFoundException ex) {
            throw new ExceptionMetier("Select failed", ex);
        } catch (SQLException ex) {
            throw new ExceptionMetier("Select failed", ex);
        }
    }
}
